
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/29/2011 13:56:14
-- Generated from EDMX file: C:\Users\stewarts\Documents\Visual Studio 2010\Projects\APH.Parliament2.0.Data\Branches\Refactor\Source\APH.Parl2.Data.Model.Parl2\Parl2.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Parl2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ParliamentarianElectorateOffice]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ElectorateOffices] DROP CONSTRAINT [FK_ParliamentarianElectorateOffice];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianPositions_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ParliamentarianPositions] DROP CONSTRAINT [FK_ParliamentarianPositions_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianPositions_Position]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ParliamentarianPositions] DROP CONSTRAINT [FK_ParliamentarianPositions_Position];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianParty]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Parliamentarians] DROP CONSTRAINT [FK_ParliamentarianParty];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Parliamentarians]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Parliamentarians];
GO
IF OBJECT_ID(N'[dbo].[Positions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Positions];
GO
IF OBJECT_ID(N'[dbo].[ElectorateOffices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ElectorateOffices];
GO
IF OBJECT_ID(N'[dbo].[ParliamentSessions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParliamentSessions];
GO
IF OBJECT_ID(N'[dbo].[Parties]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Parties];
GO
IF OBJECT_ID(N'[dbo].[PostcodeElectorateMaps]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PostcodeElectorateMaps];
GO
IF OBJECT_ID(N'[dbo].[ParliamentarianSearches]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParliamentarianSearches];
GO
IF OBJECT_ID(N'[dbo].[Parliaments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Parliaments];
GO
IF OBJECT_ID(N'[dbo].[ParliamentarianLinks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParliamentarianLinks];
GO
IF OBJECT_ID(N'[dbo].[ParliamentarianPositions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParliamentarianPositions];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Parliamentarians'
CREATE TABLE [dbo].[Parliamentarians] (
    [CPALId] int  NOT NULL,
    [Title] nvarchar(20)  NULL,
    [FirstName] nvarchar(50)  NULL,
    [Surname] nvarchar(50)  NULL,
    [Honorific] nvarchar(50)  NULL,
    [Electorate] nvarchar(50)  NULL,
    [State] nvarchar(4)  NULL,
    [Chamber] nvarchar(25)  NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [EmailAddress] nvarchar(100)  NULL,
    [FacebookAddress] nvarchar(100)  NULL,
    [TwitterAddress] nvarchar(100)  NULL,
    [PersonalWebsite] nvarchar(100)  NULL,
    [PartyWebsite] nvarchar(100)  NULL,
    [MPID] nvarchar(10)  NOT NULL,
    [FormerMember] bit  NOT NULL,
    [Gender] nvarchar(max)  NOT NULL,
    [PreferredName] nvarchar(max)  NULL,
    [PartyRecId] int  NOT NULL,
    [OtherNames] nvarchar(max)  NULL
);
GO

-- Creating table 'Positions'
CREATE TABLE [dbo].[Positions] (
    [FullTitle] nvarchar(120)  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'ElectorateOffices'
CREATE TABLE [dbo].[ElectorateOffices] (
    [AddressLine1] nvarchar(100)  NULL,
    [AddressLine2] nvarchar(100)  NULL,
    [Suburb] nvarchar(50)  NULL,
    [State] nvarchar(4)  NULL,
    [PostCode] nvarchar(4)  NULL,
    [PrincipalOffice] bit  NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [PostalAddress] nvarchar(100)  NULL,
    [PostalSuburb] nvarchar(50)  NULL,
    [PostalState] nvarchar(4)  NULL,
    [PostalPostCode] nvarchar(4)  NULL,
    [Id] int  NOT NULL,
    [Parliamentarian_CPALId] int  NOT NULL
);
GO

-- Creating table 'ParliamentSessions'
CREATE TABLE [dbo].[ParliamentSessions] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [DateFrom] datetime  NOT NULL,
    [DateTo] datetime  NULL
);
GO

-- Creating table 'Parties'
CREATE TABLE [dbo].[Parties] (
    [RecId] int  NOT NULL,
    [FullName] nvarchar(max)  NOT NULL,
    [ShortName] nvarchar(max)  NOT NULL,
    [Precedence] int  NOT NULL,
    [AbbreviatedName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PostcodeElectorateMaps'
CREATE TABLE [dbo].[PostcodeElectorateMaps] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Electorate] nvarchar(50)  NOT NULL,
    [Postcode] nvarchar(4)  NOT NULL,
    [State] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'ParliamentarianSearches'
CREATE TABLE [dbo].[ParliamentarianSearches] (
    [CPALId] int  NOT NULL,
    [FirstName] nvarchar(50)  NULL,
    [PreferredName] nvarchar(max)  NULL,
    [OtherNames] nvarchar(max)  NULL,
    [Surname] nvarchar(50)  NULL,
    [Title] nvarchar(20)  NULL,
    [Honorific] nvarchar(50)  NULL,
    [Electorate] nvarchar(50)  NULL,
    [State] nvarchar(4)  NULL,
    [Chamber] nvarchar(25)  NULL,
    [MPID] nvarchar(10)  NOT NULL,
    [FormerMember] bit  NOT NULL,
    [Gender] nvarchar(max)  NOT NULL,
    [PartyFullName] nvarchar(max)  NULL,
    [FirstTitle] varchar(100)  NULL,
    [AllTitles] varchar(1000)  NULL,
    [EmailAddress] nvarchar(100)  NULL,
    [FacebookAddress] nvarchar(100)  NULL,
    [TwitterAddress] nvarchar(100)  NULL,
    [PersonalWebsite] nvarchar(100)  NULL,
    [PartyAbbreviatedName] nvarchar(max)  NULL
);
GO

-- Creating table 'Parliaments'
CREATE TABLE [dbo].[Parliaments] (
    [ParliamentNumber] int  NOT NULL,
    [DateFrom] datetime  NULL,
    [DateTo] datetime  NULL
);
GO

-- Creating table 'ParliamentarianLinks'
CREATE TABLE [dbo].[ParliamentarianLinks] (
    [CPALId] int  NOT NULL,
    [MPID] nvarchar(10)  NOT NULL,
    [Title] nvarchar(20)  NULL,
    [FirstName] nvarchar(50)  NULL,
    [Surname] nvarchar(50)  NULL,
    [PartyWebsite] nvarchar(100)  NULL,
    [PersonalWebsite] nvarchar(100)  NULL,
    [FacebookAddress] nvarchar(100)  NULL,
    [TwitterAddress] nvarchar(100)  NULL
);
GO

-- Creating table 'ParliamentarianPositions'
CREATE TABLE [dbo].[ParliamentarianPositions] (
    [Parliamentarian_CPALId] int  NOT NULL,
    [Positions_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CPALId] in table 'Parliamentarians'
ALTER TABLE [dbo].[Parliamentarians]
ADD CONSTRAINT [PK_Parliamentarians]
    PRIMARY KEY CLUSTERED ([CPALId] ASC);
GO

-- Creating primary key on [Id] in table 'Positions'
ALTER TABLE [dbo].[Positions]
ADD CONSTRAINT [PK_Positions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ElectorateOffices'
ALTER TABLE [dbo].[ElectorateOffices]
ADD CONSTRAINT [PK_ElectorateOffices]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ParliamentSessions'
ALTER TABLE [dbo].[ParliamentSessions]
ADD CONSTRAINT [PK_ParliamentSessions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [RecId] in table 'Parties'
ALTER TABLE [dbo].[Parties]
ADD CONSTRAINT [PK_Parties]
    PRIMARY KEY CLUSTERED ([RecId] ASC);
GO

-- Creating primary key on [Id] in table 'PostcodeElectorateMaps'
ALTER TABLE [dbo].[PostcodeElectorateMaps]
ADD CONSTRAINT [PK_PostcodeElectorateMaps]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [CPALId] in table 'ParliamentarianSearches'
ALTER TABLE [dbo].[ParliamentarianSearches]
ADD CONSTRAINT [PK_ParliamentarianSearches]
    PRIMARY KEY CLUSTERED ([CPALId] ASC);
GO

-- Creating primary key on [ParliamentNumber] in table 'Parliaments'
ALTER TABLE [dbo].[Parliaments]
ADD CONSTRAINT [PK_Parliaments]
    PRIMARY KEY CLUSTERED ([ParliamentNumber] ASC);
GO

-- Creating primary key on [CPALId] in table 'ParliamentarianLinks'
ALTER TABLE [dbo].[ParliamentarianLinks]
ADD CONSTRAINT [PK_ParliamentarianLinks]
    PRIMARY KEY CLUSTERED ([CPALId] ASC);
GO

-- Creating primary key on [Parliamentarian_CPALId], [Positions_Id] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [PK_ParliamentarianPositions]
    PRIMARY KEY NONCLUSTERED ([Parliamentarian_CPALId], [Positions_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Parliamentarian_CPALId] in table 'ElectorateOffices'
ALTER TABLE [dbo].[ElectorateOffices]
ADD CONSTRAINT [FK_ParliamentarianElectorateOffice]
    FOREIGN KEY ([Parliamentarian_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianElectorateOffice'
CREATE INDEX [IX_FK_ParliamentarianElectorateOffice]
ON [dbo].[ElectorateOffices]
    ([Parliamentarian_CPALId]);
GO

-- Creating foreign key on [Parliamentarian_CPALId] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [FK_ParliamentarianPositions_Parliamentarian]
    FOREIGN KEY ([Parliamentarian_CPALId])
    REFERENCES [dbo].[Parliamentarians]
        ([CPALId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Positions_Id] in table 'ParliamentarianPositions'
ALTER TABLE [dbo].[ParliamentarianPositions]
ADD CONSTRAINT [FK_ParliamentarianPositions_Position]
    FOREIGN KEY ([Positions_Id])
    REFERENCES [dbo].[Positions]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianPositions_Position'
CREATE INDEX [IX_FK_ParliamentarianPositions_Position]
ON [dbo].[ParliamentarianPositions]
    ([Positions_Id]);
GO

-- Creating foreign key on [PartyRecId] in table 'Parliamentarians'
ALTER TABLE [dbo].[Parliamentarians]
ADD CONSTRAINT [FK_ParliamentarianParty]
    FOREIGN KEY ([PartyRecId])
    REFERENCES [dbo].[Parties]
        ([RecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianParty'
CREATE INDEX [IX_FK_ParliamentarianParty]
ON [dbo].[Parliamentarians]
    ([PartyRecId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------